package cn.project.engineering.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Create by ankele
 * <p>
 * 2021/1/6 - 14:18
 */
public class SQLUtils {

    public static Connection getSQLConnection(String ip, String user, String pwd, String db)
    {
        Connection con = null;
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            //int t=1;
            con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + ip + ":1433/" + db + ";useunicode=true;characterEncoding=UTF-8", user, pwd);
            //加上 useunicode=true;characterEncoding=UTF-8 防止中文乱码
            //int j=0;
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 根据MAC地址查询数据
     * @param mac mac地址
     * @return 数据的map
     */
    public static HashMap<String,Object> queryData(String mac) {
        HashMap<String,Object> result = new HashMap<>();
        try {
            Connection conn = getSQLConnection("db132-203.bbj.vh.cnolnic.org", "kingshiyi", "Kwerp_123", "kingshiyi");//远程连接的端口一定要查看电脑的，而不是手机的
            String sql = "SELECT A.FNumber WorkID,A.FName MachineName,B.FName LineName,W.FName WorkName,F.FName FactoryName,K.Notice FROM Machine A LEFT JOIN [Line] B ON A.[LineNO]=B.FNumber LEFT JOIN Workshop W ON B.WorkNO=W.FNumber LEFT JOIN [Factory] F ON W.FactoryNO=F.FNumber LEFT JOIN KW001 K ON A.FNumber=K.MachineNO where A.MacAdd = "+"'"+mac+"'";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {

                String workID = rs.getString("WorkID");
                result.put("workID",workID);
                String workName = rs.getString("WorkName");
                result.put("workName",workName);
                String notice = rs.getString("Notice");
                result.put("notice",notice);
                String factoryname = rs.getString("FactoryName");
                result.put("factoryname",factoryname);
                String linename = rs.getString("LineName");
                result.put("linename",linename);
                String machinename = rs.getString("MachineName");
                result.put("machinename",machinename);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static HashMap<String,Object> queryPdf(String mac){
        HashMap<String,Object> result = new HashMap<>();
        try {
            Connection conn = getSQLConnection("db132-203.bbj.vh.cnolnic.org", "kingshiyi", "Kwerp_123", "kingshiyi");//远程连接的端口一定要查看电脑的，而不是手机的
            String sql = "select * from PDFList where Mac = "+"'"+mac+"'";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String sMac = rs.getString("Mac");
                String file = rs.getString("PDFFile");
                int isNew = rs.getInt("IsNew");
                result.put("mac",sMac);
                result.put("file",file);
                result.put("isNew",isNew);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static HashMap<String,Object> queryBanner(String mac,int IsVideo){
        HashMap<String,Object> result = new HashMap<>();
        try {
            Connection conn = getSQLConnection("db132-203.bbj.vh.cnolnic.org", "kingshiyi", "Kwerp_123", "kingshiyi");//远程连接的端口一定要查看电脑的，而不是手机的
            String sql = " SELECT TOP 1 case when t1.IsVideo=0 then (t2.Imgurl+'/'+ t2.FileName) else t2.ImgUrl end  as Imgurl,t2.Pages,t1.Pages Newpages FROM Tasks t1 left join FileList t2 on t1.FileNO=t2.FileNO left join TasksDetail t3 on t1.FBillNO=t3.FBillNO where t3.FStatus=0 and t1.SDate<=getdate() and t3.MacAdd= "+"'"+mac+"' and t1.IsVideo= "+IsVideo+ " ORDER BY t1.ID DESC";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String sImgurl = rs.getString("Imgurl");
                String newpages = rs.getString("Newpages");
                int Pages = rs.getInt("Pages");
                result.put("imgurl",sImgurl);
                result.put("newpages",newpages);
                result.put("pages",Pages);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static List<HashMap<String,Object>> queryMenu(){
        List<HashMap<String,Object>> menus = new ArrayList<>();
        try {
            HashMap<String,Object> result;
            Connection conn = getSQLConnection("db132-203.bbj.vh.cnolnic.org", "kingshiyi", "Kwerp_123", "kingshiyi");//远程连接的端口一定要查看电脑的，而不是手机的
            String sql = "select * from MenuList";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                result = new HashMap<>();
                String menuName = rs.getString("MenuName");
                String webUrl = rs.getString("WebUrl");
                result.put("menuName",menuName);
                result.put("webUrl",webUrl);
                menus.add(result);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return menus;
    }

    public static boolean UpdateStatus(String mac){
        boolean rs=false;
        try {
            Connection conn = getSQLConnection("db132-203.bbj.vh.cnolnic.org", "kingshiyi", "Kwerp_123", "kingshiyi");//远程连接的端口一定要查看电脑的，而不是手机的
            String sql = "UPDATE TasksDetail SET FStatus=1 where MacAdd='"+ mac +"'";
            Statement stmt = conn.createStatement();
            rs = stmt.execute(sql);
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

} 